drop database QLbanhang
create database QLbanhang
go
use QLbanhang 

select * from DiaChiKho
drop table VaiTroQL
drop table NhanVien
drop table KhachHang
drop table NhaPhanPhoi
drop table DiaChiKho
drop table CacKhuVuc
drop table DonVi
drop table NhomHang
drop table HangHoa
drop table DieuKhoanThanhToan
drop table NhapHang
drop table XuatHang

create table VaiTroQL
(
	MaVT int identity constraint PK_MaVT primary key,
	VaiTro Nvarchar(30) not null
)
create table CacNganHang
(
	MaNH int identity constraint PK_MaNH primary key,
	TenNH Nvarchar (30),
)
go
create table NhanVien
(
	MaNV int identity constraint PK_MaNV primary key,
	HoTenNV Nvarchar(50) not null,
	GioiTinh Nvarchar(15) not null,
	NgaySinh datetime not null,
	DiaChi Nvarchar(100) not null,
	DienThoai varchar(20),
	Email varchar(50) not null,
	SoTaiKhoan varchar(20),
	TaiKhoan varchar(32),
	MatKhau varchar(32),
	NganHang int constraint PK_NganHang references CacNganHang(MaNH),
	VaiTroql int constraint FK_VaiTroql references VaiTroQL(MaVT),
)

create table KhachHang
(
	MaKH int identity constraint PK_MaKH primary key,
	HoTenKH Nvarchar(50) not null,
	DiaChi Nvarchar(100) not null,
	DienThoai varchar(20),
	Fax varchar(30),
	Email varchar(50),
	SoTaiKhoan varchar(20),
	NganHang int constraint PK_KHNganHang references CacNganHang(MaNH),
)

create table CacKhuVuc
(
	MaKV int identity constraint PK_MaKV primary key,
	TenKV Nvarchar (30),
)
create table NhaPhanPhoi
(
	MaCC int identity constraint PK_MaCC primary key,
	NhaCungCap Nvarchar(100),	
	DiaChi Nvarchar(100) not null,
	DienThoai varchar(20),
	Fax varchar(30), 
	Email varchar(50) not null,
	SoTaiKhoan varchar(20),
	NganHang int constraint PKNPP_NganHang references CacNganHang(MaNH),
	KhuVuc int constraint FK_KhuVuc references CacKhuVuc(MaKV),   
)
create table DiaChiKho
(
	MaK int identity constraint PK_MaK primary key,
	TenKho Nvarchar(50) not null,
	DiaChi Nvarchar(50) not null,
	DienThoai varchar(20),
	LienHe Nvarchar(50)
)
create table DonVi
(
	MaDV int identity constraint PK_MADV primary key,
	TenDV Nvarchar(20) not null,  
)
create table NhomHang
(
	MaNH int identity constraint PK_MaHH primary key,
	TenNH Nvarchar(100) not null,
	DVNH int constraint FK_MaDV references DonVi(MaDV),   
)
create table HangHoa
(
	MaSP int identity constraint PK_MaSP primary key,
	NhomSP int constraint FK_NhomSP references NhomHang(MaNH),   
	TenSP nvarchar(100) not null,
	DonViSP int constraint FK_DonViSP references DonVi(MaDV),
	SoLuong int not null,
	Kho int constraint FK_Kho references DiaCHiKho(MaK),  
	GiaVon int not null,
	GiaBan int not null, 
		 
)
create table DieuKhoanThanhToan
(
	MaDK int identity constraint PK_MaDK primary key,
	TenDK Nvarchar(30) not null,
)
create table NhapHang
(
	NhapHang_ID int identity constraint PK_NhapHang_ID primary key,
	HangHoa_ID int,
	TenHangHoa Nvarchar(50),
	KhoHang Nvarchar(20),
	TenDonVi Nvarchar(20),
	DonGia int,
	SoLuong int,
	ThanhTien int,
	NhanVienNH Nvarchar(50),
	NgayNhapHang datetime,
	NhaPhanPhoi_ID int,
	TenNPP Nvarchar(50),
	CachThanhToan Nvarchar(30),
	HanThanToan datetime,
	LoaiChungTu Nvarchar(50),
	ChungTu Nvarchar(20),
	GhiChu NVarchar(50)		
)
select * from NhapHang
create table XuatHang
(
	XuatHang_ID int identity constraint PK_XuatHang_ID primary key,
	HangHoa_ID int,
	TenHangHoa Nvarchar(50),
	KhoHang Nvarchar(20),
	TenDonVi Nvarchar(20),
	DonGia int,
	SoLuong int,
	ThanhTien int,
	NhanVienXH Nvarchar(50),
	NgayXuatHang datetime,
	KhachHang_ID int,
	TenKHXH Nvarchar(50),
	CachThanhToan Nvarchar(30),
	HanThanToan datetime,
	LoaiChungTu Nvarchar(50),
	ChungTu Nvarchar(20),
	GhiChu NVarchar(50)
)

create table LienHe(
	id int,
	TenCongTy Nvarchar(100) not null,
	DiaChi Nvarchar(100) not null,
	DienThoai varchar(20) not null,
	Fax varchar(20),
	Email varchar(50) not null,
	Website varchar(100),
	LinhVuc Nvarchar(50),
	NganhNghe Nvarchar(50),
	MaSoThue varchar(20),
	NguoiLienHe Nvarchar(50),
	NoiDung Nvarchar(500), 
)
create table KetThucGDMuaHang(
	ID_GDMuaHang int identity constraint PK_ID_GDMuaHang primary key,
	ID_NhaPP int,
	TenNhaPP Nvarchar(50),
	ChungTu varchar(20),
	Ngay datetime,
	TongThanhToan int,
	DaTra int,
	ConLai int,
	GhiChu NVarchar(50),
	LoaiChungTu Nvarchar(50),
)

create table KetThucGDBanHang(
	ID_GDBanHang int identity constraint PK_ID_GDBanHang primary key,
	ID_KhachHang int,
	TenKH Nvarchar(50),
	ChungTu varchar(20),
	Ngay datetime,
	TongThanhToan float,
	DaTra float,
	ConLai float,
	GhiChu NVarchar(50),
	LoaiChungTu Nvarchar(50),
)
select sum(TongThanhToan) from KetThucGDBanHang
select * from KetThucGDBanHang order by ID_GDBanHang DESC
select * from KetThucGDMuaHang
select top 1 ID_GDMuaHang from (select top (select count(*) from KetThucGDMuaHang) * from KetThucGDMuaHang order by ID_GDMuaHang DESC)a
select top 1 ID_GDBanHang from (select top (select count(*) from KetThucGDBanHang) * from KetThucGDBanHang order by ID_GDBanHang DESC)a

select * from NhapHang order by NhapHang_ID DESC
Delete FROM NhapHang WHERE NhapHang_ID =1 order by NhapHang_ID DES

select top 1 NhapHang_ID from (select top (select count(*) from NhapHang) * from NhapHang order by NhapHang_ID DESC)a

SELECT * FROM NhapHang WHERE NgayNhapHang BETWEEN '01-10-2011' and '06-11-2011'
select * from DieuKhoanThanhToan
select Ngay,Sum(TongThanhToan)as TongThanhToan  from KetThucGDBanHang where Ngay BETWEEN'04-10-2011' AND '08-10-2011' group by Ngay
select * from KetThucGDMuaHang
select * from NhapHang
select * from NhapHang where NgayNhapHang >= '04-11-2011' 
select * from NhapHang where NgayNhapHang >= '01-10-2011' and NgayNhapHang <='06-11-2011'
SELECT * FROM NhapHang WHERE left(NgayNhapHang,10) between '01-10-2011' and '06-11-2011'

SELECT * FROM NhapHang WHERE NgayNhapHang > '27-10-2011' or NgayNhapHang < '22-11-2011'

